<!DOCTYPE html>
<html lang="en-us">
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    
<meta charset="UTF-8">
<title>Configuration | Elasticsearch Guide [7.7] | Elastic</title>
<link rel="home" href="index.html" title="Elasticsearch Guide [7.7]">
<link rel="up" href="sql-odbc.html" title="SQL ODBC">
<link rel="prev" href="sql-odbc-installation.html" title="Driver installation">
<link rel="next" href="sql-client-apps.html" title="SQL Client Applications">
<meta name="DC.type" content="Learn/Docs/Elasticsearch/Reference/7.7">
<meta name="DC.subject" content="Elasticsearch">
<meta name="DC.identifier" content="7.7">
<meta name="robots" content="noindex,nofollow">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://cdn.optimizely.com/js/18132920325.js"></script>
    <link rel="apple-touch-icon" sizes="57x57" href="/apple-icon-57x57.png">
    <link rel="apple-touch-icon" sizes="60x60" href="/apple-icon-60x60.png">
    <link rel="apple-touch-icon" sizes="72x72" href="/apple-icon-72x72.png">
    <link rel="apple-touch-icon" sizes="76x76" href="/apple-icon-76x76.png">
    <link rel="apple-touch-icon" sizes="114x114" href="/apple-icon-114x114.png">
    <link rel="apple-touch-icon" sizes="120x120" href="/apple-icon-120x120.png">
    <link rel="apple-touch-icon" sizes="144x144" href="/apple-icon-144x144.png">
    <link rel="apple-touch-icon" sizes="152x152" href="/apple-icon-152x152.png">
    <link rel="apple-touch-icon" sizes="180x180" href="/apple-icon-180x180.png">
    <link rel="icon" type="image/png" href="/favicon-32x32.png" sizes="32x32">
    <link rel="icon" type="image/png" href="/android-chrome-192x192.png" sizes="192x192">
    <link rel="icon" type="image/png" href="/favicon-96x96.png" sizes="96x96">
    <link rel="icon" type="image/png" href="/favicon-16x16.png" sizes="16x16">
    <link rel="manifest" href="/manifest.json">
    <meta name="apple-mobile-web-app-title" content="Elastic">
    <meta name="application-name" content="Elastic">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="msapplication-TileImage" content="/mstile-144x144.png">
    <meta name="theme-color" content="#ffffff">
    <meta name="naver-site-verification" content="936882c1853b701b3cef3721758d80535413dbfd">
    <meta name="yandex-verification" content="d8a47e95d0972434">
    <meta name="localized" content="true">
    <meta name="st:robots" content="follow,index">
    <meta property="og:image" content="https://www.elastic.co/static/images/elastic-logo-200.png">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
    <link rel="icon" href="/favicon.ico" type="image/x-icon">
    <link rel="apple-touch-icon-precomposed" sizes="64x64" href="/favicon_64x64_16bit.png">
    <link rel="apple-touch-icon-precomposed" sizes="32x32" href="/favicon_32x32.png">
    <link rel="apple-touch-icon-precomposed" sizes="16x16" href="/favicon_16x16.png">
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
    <link rel="stylesheet" type="text/css" href="/guide/static/styles.css">
  </head>

  <!--© 2015-2021 Elasticsearch B.V. Copying, publishing and/or distributing without written permission is strictly prohibited.-->

  <body>
    <!-- Google Tag Manager -->
    <script>dataLayer = [];</script><noscript><iframe src="//www.googletagmanager.com/ns.html?id=GTM-58RLH5" height="0" width="0" style="display:none;visibility:hidden"></iframe></noscript>
    <script>(function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= '//www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f); })(window,document,'script','dataLayer','GTM-58RLH5');</script>
    <!-- End Google Tag Manager -->

    <!-- Global site tag (gtag.js) - Google Analytics -->
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-12395217-16"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-12395217-16');
    </script>

    <!--BEGIN QUALTRICS WEBSITE FEEDBACK SNIPPET-->
    <script type="text/javascript">
      (function(){var g=function(e,h,f,g){
      this.get=function(a){for(var a=a+"=",c=document.cookie.split(";"),b=0,e=c.length;b<e;b++){for(var d=c[b];" "==d.charAt(0);)d=d.substring(1,d.length);if(0==d.indexOf(a))return d.substring(a.length,d.length)}return null};
      this.set=function(a,c){var b="",b=new Date;b.setTime(b.getTime()+6048E5);b="; expires="+b.toGMTString();document.cookie=a+"="+c+b+"; path=/; "};
      this.check=function(){var a=this.get(f);if(a)a=a.split(":");else if(100!=e)"v"==h&&(e=Math.random()>=e/100?0:100),a=[h,e,0],this.set(f,a.join(":"));else return!0;var c=a[1];if(100==c)return!0;switch(a[0]){case "v":return!1;case "r":return c=a[2]%Math.floor(100/c),a[2]++,this.set(f,a.join(":")),!c}return!0};
      this.go=function(){if(this.check()){var a=document.createElement("script");a.type="text/javascript";a.src=g;document.body&&document.body.appendChild(a)}};
      this.start=function(){var a=this;window.addEventListener?window.addEventListener("load",function(){a.go()},!1):window.attachEvent&&window.attachEvent("onload",function(){a.go()})}};
      try{(new g(100,"r","QSI_S_ZN_emkP0oSe9Qrn7kF","https://znemkp0ose9qrn7kf-elastic.siteintercept.qualtrics.com/WRSiteInterceptEngine/?Q_ZID=ZN_emkP0oSe9Qrn7kF")).start()}catch(i){}})();
    </script><div id="ZN_emkP0oSe9Qrn7kF"><!--DO NOT REMOVE-CONTENTS PLACED HERE--></div>
    <!--END WEBSITE FEEDBACK SNIPPET-->

    <div id="elastic-nav" style="display:none;"></div>
    <script src="https://www.elastic.co/elastic-nav.js"></script>

    <!-- Subnav -->
    <div>
      <div>
        <div class="tertiary-nav d-none d-md-block">
          <div class="container">
            <div class="p-t-b-15 d-flex justify-content-between nav-container">
              <div class="breadcrum-wrapper"><span><a href="/guide/" style="font-size: 14px; font-weight: 600; color: #000;">Docs</a></span></div>
            </div>
          </div>
        </div>
      </div>
    </div>

    <div class="main-container">
      <section id="content">
        <div class="content-wrapper">

          <section id="guide" lang="en">
            <div class="container">
              <div class="row">
                <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                  <!-- start body -->
                  <div class="page_header">
<strong>IMPORTANT</strong>: No additional bug fixes or documentation updates
will be released for this version. For the latest information, see the
<a href="../current/index.html">current release documentation</a>.
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-odbc.html">SQL ODBC</a></span>
»
<span class="breadcrumb-node">Configuration</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-odbc-installation.html">« Driver installation</a>
</span>
<span class="next">
<a href="sql-client-apps.html">SQL Client Applications »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-odbc-setup"></a>Configuration<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a><a class="xpack_tag" href="/subscriptions"></a>
</h2>
</div></div></div>
<p>Once the driver has been installed, in order for an application to be able to connect to Elasticsearch through ODBC, a set of configuration parameters must be provided to the driver. Depending on the application, there are generally three ways of providing these parameters:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
through a connection string;
</li>
<li class="listitem">
using a User DSN or System DSN;
</li>
<li class="listitem">
through a File DSN.
</li>
</ul>
</div>
<p>DSN (<em>data source name</em>) is a generic name given to the set of parameters an ODBC driver needs to connect to a database.</p>
<p>We will refer to these parameters as <em>connection parameters</em> or <em>DSN</em> (despite some of these parameters configuring some other aspects of a driver’s functions; e.g. logging, buffer sizes…​).</p>
<p>Using a DSN is the most widely used, simplest and safest way of performing the driver configuration. Constructing a connection string, on the other hand, is the most crude way and consequently the least common method.</p>
<p>We will focus on DSN usage only.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="data-source-administrator"></a>1. Launching ODBC Data Source Administrator<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h3>
</div></div></div>
<p>For DSN management, ODBC provides the <em>ODBC Data Source Administrator</em> application, readily installed on all recent desktop Windows operating systems.</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
The 32-bit version of the Odbcad32.exe file is located in the <code class="literal">%systemdrive%\Windows\SysWoW64</code> folder.
</li>
<li class="listitem">
The 64-bit version of the Odbcad32.exe file is located in the <code class="literal">%systemdrive%\Windows\System32</code> folder.
</li>
</ul>
</div>
<p>To launch it, open the search menu - <em>Win + S</em> - and type "ODBC Data Sources (64-bit)" or "ODBC Data Sources (32-bit)" and press <em>Enter</em>:</p>
<p><a id="launch_administrator"></a><strong>Launching ODBC Data Source Administrator</strong><span class="image"><img src="images/sql/odbc/launch_administrator.png" alt="launch administrator"></span></p>
<p>Once launched, you can verify that the driver was installed correctly by clicking on the <em>Drivers</em> tab of the ODBC Data Source Administrator and checking that <em>Elasticsearch Driver</em> is present in the list of installed drivers.</p>
<p>You should also see the version number of the installed driver.</p>
<p><a id="administrator_drivers"></a><strong>Drivers tab</strong><span class="image"><img src="images/sql/odbc/administrator_drivers.png" alt="administrator drivers"></span></p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="dsn-configuration"></a>2. Configure a DSN<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The next step is to configure a DSN. You can choose between the following options mapped on the first three tabs of the Administrator application:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<p>User DSN</p>
<p>The connections configured under this tab are only available to the currently logged in user. Each of these DSNs are referred to by a chosen arbitrary name (typically a host or cluster name).</p>
<p>The actual set of parameters making up the DSN is stored through the driver in the system registry. Thus, a user will later only need to provide an application with the DSN name in order to connect to the configured Elasticsearch instance.</p>
</li>
<li class="listitem">
<p>System DSN</p>
<p>Similar to a User DSN, except that the connections configured under this tab will be available to all the users configured on the system.</p>
</li>
<li class="listitem">
<p>File DSN</p>
<p>This tab contains functionality that will allow to have one set of connection parameters written into a file, rather then the Registry.</p>
<p>Such a file can be then shared among multiple systems and the user will need to specify the path to it, in order to have the application connect to the configured Elasticsearch instance.</p>
</li>
</ul>
</div>
<p>The configuration steps are similar for all the above points. Following is an example of configuring a System DSN.</p>
<h5>
<a id="_2_1_launch_elasticsearch_sql_odbc_driver_dsn_editor"></a>2.1 Launch Elasticsearch SQL ODBC Driver DSN Editor<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>Click on the <em>System DSN</em> tab, then on the <em>Add…​</em> button:</p>
<p><a id="system_add"></a><strong>Add a new DSN</strong><span class="image"><img src="images/sql/odbc/administrator_system_add.png" alt="administrator system add"></span></p>
<p>A new window will open, listing all available installed drivers. Click on <em>Elasticsearch Driver</em>, to highlight it, then on the <em>Finish</em> button:</p>
<p><a id="launch_editor"></a><strong>Launch the DSN Editor</strong><span class="image"><img src="images/sql/odbc/administrator_launch_editor.png" alt="administrator launch editor"></span></p>
<p>This action closes the previously opened second window and open a new one instead, Elasticsearch SQL ODBC Driver’s DSN Editor:</p>
<p><a id="dsn_editor"></a><strong>Elasticsearch SQL ODBC Driver DSN Editor</strong><span class="image"><img src="images/sql/odbc/dsn_editor_basic.png" alt="dsn editor basic"></span></p>
<p>This new window has three tabs, each responsible for a set of configuration parameters, as follows.</p>
<h5>
<a id="_2_2_connection_parameters"></a>2.2 Connection parameters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>This tab allows configuration for the following items:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<p>Name</p>
<p>This is the name the DSN will be referred by.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The characters available for this field are limited to the set permitted for a Registry key.</p>
</div>
</div>
<p>Example: <em>localhost</em></p>
</li>
<li class="listitem">
<p>Description</p>
<p>This field allows a arbitrary text; generally used for short notes about the configured connection.</p>
<p>Example: <em>Clear-text connection to the local [::1]:9200.</em></p>
</li>
<li class="listitem">
<p>Cloud ID</p>
<p>The <em>Cloud ID</em> is a string that simplifies the configuration when connecting to
Elastic’s Cloud Elasticsearch Service; it is obtained from within the Cloud console of each
Elasticsearch cluster and encodes the connection parameters to that cluster.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>When this field is provisioned, the <em>Hostname</em>, <em>Port</em> and the security
settings are provisioned as well and their respective inputs disabled.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Hostname</p>
<p>This field requires an IP address or a resolvable DNS name of the Elasticsearch instance that the driver will connect to.</p>
<p>Example: <em>::1</em></p>
</li>
<li class="listitem">
<p>Port</p>
<p>The port on which the Elasticsearch listens on.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>If left empty, the default <span class="strong strong"><strong>9200</strong></span> port number will be used.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Username, Password</p>
<p>If security is enabled, these fields will need to contain the credentials of the access user.</p>
</li>
</ul>
</div>
<p>At a minimum, the <em>Name</em> and <em>Hostname</em> fields must be provisioned, before the DSN can be saved.</p>
<div class="warning admon">
<div class="icon"></div>
<div class="admon_content">
<p>Connection encryption is enabled by default. This will need to be changed if connecting to an Elasticsearch node with no encryption.</p>
</div>
</div>
<h5>
<a id="_2_3_cryptography_parameters"></a>2.3 Cryptography parameters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>One of the following SSL options can be chosen:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<p>Disabled. All communications unencrypted.</p>
<p>The communication between the driver and the Elasticsearch instance is performed over a clear-text connection.</p>
<div class="warning admon">
<div class="icon"></div>
<div class="admon_content">
<p>This setting can expose the access credentials to a 3rd party intercepting the network traffic and is not recommended.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Enabled. Certificate not validated.</p>
<p>The connection encryption is enabled, but the certificate of the server is not validated.</p>
<p>This is currently the default setting.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>This setting allows a 3rd party to act with ease as a man-in-the-middle and thus intercept all communications.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Enabled. Certificate is validated; hostname not validated.</p>
<p>The connection encryption is enabled and the driver verifies that server’s certificate is valid, but it does <span class="strong strong"><strong>not</strong></span> verify if the
certificate is running on the server it was meant for.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>This setting allows a 3rd party that had access to server’s certificate to act as a man-in-the-middle and thus intercept all the
communications.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Enabled. Certificate is validated; hostname validated.</p>
<p>The connection encryption is enabled and the driver verifies that both the certificate is valid, as well as that it is being deployed on
the server that the certificate was meant for.</p>
</li>
<li class="listitem">
<p>Enabled. Certificate identity chain validated.</p>
<p>This setting is equivalent to the previous one, with one additional check against certificate’s revocation. This offers the strongest
security option and is the recommended setting for production deployments.</p>
</li>
<li class="listitem">
<p>Certificate File</p>
<p>In case the server uses a certificate that is not part of the PKI, for example using a self-signed certificate, you can configure the path to a X.509 certificate file that will be used by the driver to validate server’s offered certificate.</p>
<p>The driver will only read the contents of the file just before a connection is attempted. See <a class="xref" href="sql-odbc-setup.html#connection_testing" title="2.5 Testing the connection">2.5 Testing the connection</a> section further on how to check the validity of the provided parameters.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The certificate file can not be bundled or password protected since the driver will not prompt for a password.</p>
</div>
</div>
<p>If using the file browser to locate the certificate - by pressing the <em>Browse…​</em> button - only files with <em>.pem</em> and <em>.der</em> extensions
will be considered by default. Choose <em>All Files (*.*)</em> from the drop down, if your file ends with a different extension:</p>
<p><a id="dsn_editor_cert"></a><strong>Certificate file browser</strong><span class="image"><img src="images/sql/odbc/dsn_editor_security_cert.png" alt="dsn editor security cert"></span></p>
</li>
</ul>
</div>
<h5>
<a id="_2_4_connection_parameters"></a>2.4 Connection parameters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>The connection configuration can further be tweaked by the following parameters.</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<p>Request timeout (s)</p>
<p>The maximum number of seconds for a request to the server. The value 0 disables the timeout.
This corresponds to the <code class="literal">Timeout</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Max page size (rows)</p>
<p>The maximum number of rows that Elasticsearch SQL server should send the driver for one page.
This corresponds to the <code class="literal">MaxFetchSize</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Max page length (MB)</p>
<p>The maximum number of megabytes that the driver will accept for one page.
This corresponds to the <code class="literal">MaxBodySizeMB</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Floats format</p>
<p>How should the floating point numbers be printed, when these are converted to string by the driver.
This corresponds to the <code class="literal">ScientificFloats</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Data encoding</p>
<p>How should the data between the server and the driver be encoded as.
This corresponds to the <code class="literal">Packing</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Data compression</p>
<p>Should the data between the server and the driver be compressed?
This corresponds to the <code class="literal">Compression</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Follow HTTP redirects?</p>
<p>Should the driver follow HTTP redirects of the requests to the server?
This corresponds to the <code class="literal">Follow</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Use local timezone?</p>
<p>Should the driver use machine’s local timezone? The default is UTC.
This corresponds to the <code class="literal">ApplyTZ</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Auto-escape PVAs?</p>
<p>Should the driver auto-escape the pattern-value arguments?
This corresponds to the <code class="literal">AutoEscapePVA</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Multi value field lenient?</p>
<p>Should the server return one value out of a multi-value field (instead of rejecting the request)?
This corresponds to the <code class="literal">MultiFieldLenient</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
<li class="listitem">
<p>Include frozen indices?</p>
<p>Should the server consider the frozen indices when servicing a request?
This corresponds to the <code class="literal">IndexIncludeFrozen</code> setting in <a class="xref" href="sql-odbc-setup.html#odbc-cfg-dsnparams" title="Connection string parameters">Connection string parameters</a>.</p>
</li>
</ul>
</div>
<p><a id="dsn_editor_misc"></a><strong>Connection parameters</strong><span class="image"><img src="images/sql/odbc/dsn_editor_misc.png" alt="dsn editor misc"></span></p>
<h5>
<a id="_2_5_logging_parameters"></a>2.5 Logging parameters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>For troubleshooting purposes, the Elasticsearch SQL ODBC Driver offers functionality to log the API calls that an application makes; this is enabled in the Administrator application:</p>
<p><a id="administrator_tracing"></a><strong>Enable Application ODBC API logging</strong><span class="image"><img src="images/sql/odbc/administrator_tracing.png" alt="administrator tracing"></span></p>
<p>However, this only logs the ODBC API calls made by the application into the <em>Driver Manager</em> and not those made by the <em>Driver Manager</em> into the driver itself. To enable logging of the calls that the driver receives, as well as internal driver processing events, you can enable driver’s logging on Editor’s <em>Logging</em> tab:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<p>Enable Logging?</p>
<p>Ticking this will enable driver’s logging. A logging directory is also mandatory when this option is enabled (see the next option).
However the specified logging directory will be saved in the DSN if provided, even if logging is disabled.</p>
</li>
<li class="listitem">
<p>Log Directory</p>
<p>Here is to specify which directory to write the log files in.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The driver will create <span class="strong strong"><strong>one log file per connection</strong></span>, for those connections that generate logging messages.</p>
</div>
</div>
</li>
<li class="listitem">
<p>Log Level</p>
<p>Configure the verbosity of the logs.</p>
<p><a id="administrator_logging"></a><strong>Enable driver logging</strong><span class="image"><img src="images/sql/odbc/dsn_editor_logging.png" alt="dsn editor logging"></span></p>
<p>When authentication is enabled, the password will be redacted from the logs.</p>
</li>
</ul>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>Debug-logging can quickly lead to the creation of many very large files and generate significant processing overhead. Only enable if
instructed so and preferably only when fetching low volumes of data.</p>
</div>
</div>
<h5>
<a id="connection_testing"></a>2.5 Testing the connection<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h5>
<p>Once the <em>Hostname</em>, the <em>Port</em> (if different from implicit default) and the SSL options are configured, you can test if the provided
parameters are correct by pressing the <em>Test Connection</em> button. This will instruct the driver to connect to the Elasticsearch instance and perform
a simple SQL test query. (This will thus require a running Elasticsearch instance with the SQL plugin enabled.)</p>
<p><a id="dsn_editor_conntest"></a><strong>Connection testing</strong><span class="image"><img src="images/sql/odbc/dsn_editor_conntest.png" alt="dsn editor conntest"></span></p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>When connection testing, all the configured parameters are taken into account, including the logging configuration. This will allow
early detection of potential file/directory access rights conflicts.</p>
</div>
</div>
<p>See <a class="xref" href="sql-odbc-setup.html#alternative_logging" title="Alternative logging configuration">Alternative logging configuration</a> section further for an alternative way of configuring the logging.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="available-dsn"></a>3. DSN is available<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Once everything is in place, pressing the <em>Save</em> button will store the configuration into the chosen destination (Registry or file).</p>
<p>Before saving a DSN configuration the provided file/directory paths are verified to be valid on the current system. The DSN editor
will however not verify in any way the validity or reachability of the configured <em>Hostname</em> : <em>Port</em>. See <a class="xref" href="sql-odbc-setup.html#connection_testing" title="2.5 Testing the connection">2.5 Testing the connection</a>
for an exhaustive check.</p>
<p>If everything is correct, the name of the newly created DSN will be listed as available to use:</p>
<p><a id="system_added"></a><strong>Connection added</strong><span class="image"><img src="images/sql/odbc/administrator_system_added.png" alt="administrator system added"></span></p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="alternative_logging"></a>Alternative logging configuration<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Due to the specification of the ODBC API, the driver will receive the configured DSN parameters - including the logging ones - only once a
connection API is invoked (such as <em>SQLConnect</em> or <em>SQLDriverConnect</em>). The <em>Driver Manager</em> will however always make a set of API calls
into the driver before attempting to establish a connection. To capture those calls as well, one needs to pass logging configuration
parameters in an alternative way. The Elasticsearch SQL ODBC Driver will use an environment variable for this purpose.</p>
<p>Configuring an environment variable is OS specific and not detailed in this guide. Whether the variable should be configured system-wide
or user-specific depends on the way the ODBC-enabled application is being run and if logging should affect the current user only or not.</p>
<p>The definition of the environment variable needs to be done as follows:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
Name: <em>ESODBC_LOG_DIR</em>
</li>
<li class="listitem">
<p>Value: [path](?[level]), where:</p>
<p>[path] is the path to the directory where the log files will be written into;</p>
<p>[level] is optional and can take one of the following values: <em>debug</em>, <em>info</em>, <em>warn</em>, <em>error</em>; if not provided, <em>debug</em> is assumed.</p>
</li>
</ul>
</div>
<p><a id="env_var_logging"></a><strong>Logging environment variable</strong><span class="image"><img src="images/sql/odbc/env_var_log.png" alt="env var log"></span></p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>When enabling the logging through the environment variable, the driver will create <span class="strong strong"><strong>one log file per process</strong></span>.</p>
</div>
</div>
<p>Both ways of configuring the logging can coexist and both can use the same
destination logging directory. However, one logging message will only be logged
once, the connection logging taking precedence over the environment variable
logging.</p>
<h4>
<a id="odbc-cfg-dsnparams"></a>Connection string parameters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/endpoints/odbc/configuration.asciidoc">edit</a>
</h4>
<p>The following is a list of additional parameters that can be configured for a
particular connection, in case the default behavior of the driver is not
suitable. For earlier versions of the driver, this needs to be done within the
client application, in a manner particular to that application, generally in a
free text input box (sometimes named "Connection string", "String extras", or
similar).  The format of the string is <code class="literal">Attribute1=Value1</code>. Multiple attributes
can be specified, separated by a semicolon
<code class="literal">Attribute1=Value1;Attribute2=Value2;</code>. The attribute names are given below.</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">Timeout</code> (default: <code class="literal">0</code>)
</span>
</dt>
<dd>
The maximum time (in seconds) a request to the server can take. This can be
overridden by a larger statement-level timeout setting. The value 0 means no
timeout.
</dd>
<dt>
<span class="term">
<code class="literal">Follow</code> (default: <code class="literal">yes</code>)
</span>
</dt>
<dd>
A boolean value (<code class="literal">yes</code>|<code class="literal">no</code> / <code class="literal">true</code>|<code class="literal">false</code> / <code class="literal">0</code>|<code class="literal">1</code>) controlling if the
driver will follow HTTP redirects.
</dd>
<dt>
<span class="term">
<code class="literal">Packing</code> (default: <code class="literal">CBOR</code>)
</span>
</dt>
<dd>
<p>
This value controls which data format to encode the REST content in. Possible
values are:
</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<code class="literal">CBOR</code>: use the Concise Binary Object Representation format. This is the
preferred encoding, given its more compact format.
</li>
<li class="listitem">
<code class="literal">JSON</code>: use the JavaScript Object Notation format. This format is more
verbose, but easier to read, useful in debugging cases.
</li>
</ul>
</div>
</dd>
<dt>
<span class="term">
<code class="literal">Compression</code> (default: <code class="literal">auto</code>)
</span>
</dt>
<dd>
<p>
This value controls if and when the REST content - encoded in one of the above
formats -  is going to be compressed. The possible values are:
</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<code class="literal">on</code>: enables the compression;
</li>
<li class="listitem">
<code class="literal">off</code>: disables the compression;
</li>
<li class="listitem">
<code class="literal">auto</code>: enables the compression, except for the case when the data flows
through a secure connection; since in this case the encryption layer employs
its own data compression and there can be security implications when an
additional compression is enabled, the setting should be kept to this value.
</li>
</ul>
</div>
</dd>
<dt>
<span class="term">
<code class="literal">MaxFetchSize</code> (default: <code class="literal">0</code>)
</span>
</dt>
<dd>
The maximum number of rows that Elasticsearch SQL server should send the driver for one
page. This corresponds to Elasticsearch SQL’s request parameter <code class="literal">fetch_size</code> (see
<a class="xref" href="sql-rest-fields.html" title="Supported REST parameters">Supported REST parameters</a>). The value 0 means server default.
</dd>
<dt>
<span class="term">
<code class="literal">MaxBodySizeMB</code> (default: <code class="literal">100</code>)
</span>
</dt>
<dd>
The maximum size (in megabytes) that an answer can grow to, before being
rejected as too large by the driver.
This is concerning the HTTP answer body of one page, not the cumulated data
volume that a query might generate.
</dd>
<dt>
<span class="term">
<code class="literal">ApplyTZ</code> (default: <code class="literal">no</code>)
</span>
</dt>
<dd>
<p>
A boolean value controlling the timezone of:
</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
the context in which the query will execute (especially relevant for functions dealing with timestamp components);
</li>
<li class="listitem">
the timestamps received from / sent to the server.
If disabled, the UTC timezone will apply; otherwise, the local machine’s set
timezone.
</li>
</ul>
</div>
</dd>
<dt>
<span class="term">
<code class="literal">ScientificFloats</code> (default: <code class="literal">default</code>)
</span>
</dt>
<dd>
<p>
Controls how the floating point numbers will be printed, when these are
converted to string by the driver. Possible values given to this parameter:
</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<code class="literal">scientific</code>: the exponential notation (ex.: 1.23E01);
</li>
<li class="listitem">
<code class="literal">default</code>: the default notation (ex.: 12.3);
</li>
<li class="listitem">
<code class="literal">auto</code>: the driver will choose one of the above depending on the value to be
printed.
Note that the number of decimals is dependent on the precision (or ODBC scale)
of the value being printed and varies with the different floating point types
supported by Elasticsearch SQL.
This setting is not effective when the application fetches from the driver the
values as numbers and then does the conversion subsequently itself.
</li>
</ul>
</div>
</dd>
<dt>
<span class="term">
<code class="literal">VersionChecking</code> (default: <code class="literal">strict</code>)
</span>
</dt>
<dd>
<p>
By default, the version of the driver and that of the server must be the same.
This parameter will allow a driver to connect to a server of different version.
The variation however can only be of the minor version, both endpoints must be
of same major version number.
Possible values:
</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
<code class="literal">strict</code>: the versions must be in sync;
</li>
<li class="listitem">
<code class="literal">major</code>: the versions must have the same major number.
</li>
</ul>
</div>
</dd>
</dl>
</div>
<div class="warning admon">
<div class="icon"></div>
<div class="admon_content">
<p>This parameter can only be used for troubleshooting purposes. Running
with versions out of sync is not supported.</p>
</div>
</div>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">MultiFieldLenient</code> (default: <code class="literal">true</code>)
</span>
</dt>
<dd>
This boolean parameter controls the behavior of the server in case a
multi-value field is queried. In case this is set and the server encounters
such a field, it will pick a value in the set - without any guarantees of what
that will be, but typically the first in natural ascending order - and return
it as the value for the column. If not set, the server will return an error.
This corresponds to Elasticsearch SQL’s request parameter <code class="literal">field_multi_value_leniency</code>
(see <a class="xref" href="sql-rest-fields.html" title="Supported REST parameters">Supported REST parameters</a>).
</dd>
<dt>
<span class="term">
<code class="literal">AutoEscapePVA</code> (default: <code class="literal">true</code>)
</span>
</dt>
<dd>
The pattern-value arguments make use of <code class="literal">_</code> and <code class="literal">%</code> as special characters to
build patern matching values. Some applications however use these chars as
regular ones, which can lead to Elasticsearch SQL returning more data than the app
intended. With the auto escaping, the driver will inspect the arguments and
will escape these special characters if not already done by the application.
</dd>
<dt>
<span class="term">
<code class="literal">IndexIncludeFrozen</code> (default: <code class="literal">false</code>)
</span>
</dt>
<dd>
If this parameter is <code class="literal">true</code>, the server will include the frozen indices in the
query execution.
This corresponds to Elasticsearch SQL’s request parameter <code class="literal">index_include_frozen</code>
(see <a class="xref" href="sql-rest-fields.html" title="Supported REST parameters">Supported REST parameters</a>).
</dd>
</dl>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-odbc-installation.html">« Driver installation</a>
</span>
<span class="next">
<a href="sql-client-apps.html">SQL Client Applications »</a>
</span>
</div>
</div>

                  <!-- end body -->
                </div>
                <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                  <div id="rtpcontainer" style="display: block;">
                    <div class="mktg-promo">
                      <h3>Most Popular</h3>
                      <ul class="icons">
                        <li class="icon-elasticsearch-white"><a href="https://www.elastic.co/webinars/getting-started-elasticsearch?baymax=default&amp;elektra=docs&amp;storm=top-video">Get Started with Elasticsearch: Video</a></li>
                        <li class="icon-kibana-white"><a href="https://www.elastic.co/webinars/getting-started-kibana?baymax=default&amp;elektra=docs&amp;storm=top-video">Intro to Kibana: Video</a></li>
                        <li class="icon-logstash-white"><a href="https://www.elastic.co/webinars/introduction-elk-stack?baymax=default&amp;elektra=docs&amp;storm=top-video">ELK for Logs &amp; Metrics: Video</a></li>
                      </ul>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          </section>

        </div>


<div id="elastic-footer"></div>
<script src="https://www.elastic.co/elastic-footer.js"></script>
<!-- Footer Section end-->

      </section>
    </div>

<script src="/guide/static/jquery.js"></script>
<script type="text/javascript" src="/guide/static/docs.js"></script>
<script type="text/javascript">
  window.initial_state = {}</script>
  </body>
</html>
